﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MT.Enterprise.Utils.Extensions;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Entities.BPM_Engine;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.UserTrial;
using NP.BPMReportPlatform.UserTrial.Dto;
using NP.BPMReportPlatform.UserTrial.Enum;
using NP.BPMReportPlatform.Utils;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;
using Volo.Abp.Application.Dtos;

namespace NP.BPMReportPlatform.UserTrial
{
    /// <summary>
    /// 试用期服务
    /// </summary>
    public class UserTrialService : BPMReportPlatformAppService, IUserTrialService
    {
        #region Variables

        private readonly SqlSugarClient _db;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        private readonly SqlSugarClient _dbEngine;

        #endregion

        #region Constructor

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sqlSugarAppService"></param>
        public UserTrialService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
        }

        #endregion

        #region 试用期结果汇总

        /// <summary>
        /// 试用期员结果汇总查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<GetSummarResultOutput>> GetSummarResult(GetSummarResultInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = _db.Queryable<TrialUsers, TrialUserTripEntity>(
                    (r, t) => new JoinQueryInfos(
                        JoinType.Left, r.WorkNumber == t.WorkNumber))
                .Where((r, t) => r.TU)
                .WhereIF(!input.WorkNumber.IsNullOrEmpty(), (r, t) => r.WorkNumber.Contains(input.WorkNumber))
                .WhereIF(!input.UserName.IsNullOrEmpty(), (r, t) => r.UserName.Contains(input.UserName))
                .WhereIF(!input.Mentor.IsNullOrEmpty(), (r, t) =>
                r.MentorCode.Contains(input.Mentor) ||
                r.MentorName.Contains(input.Mentor) ||
                r.HighMentorCode.Contains(input.Mentor) ||
                r.HighMentorName.Contains(input.Mentor)
                )
                  .WhereIF(input.JoinStartDate != null, (r, t) => input.JoinStartDate <= r.JoinDate)
                  .WhereIF(input.JoinEndDate != null, (r, t) => input.JoinEndDate >= r.JoinDate)
                  .WhereIF(input.RegularStartDate != null, (r, t) => input.RegularStartDate <= r.RegularDate)
                  .WhereIF(input.RegularEndDate != null, (r, t) => input.RegularEndDate >= r.RegularDate)
                  .WhereIF(input.Round != null && input.Round != 100, (r, t) => r.Status == input.Round)
                  .WhereIF(input.Round != null && input.Round == 100, (r, t) => r.Status < 6)
                  .WhereIF(input.Department != null, (r, t) => r.FullPathText.Contains(input.Department))

                .Select((r, t) =>
                    new GetSummarResultOutput()
                    {
                        WorkNumber = r.WorkNumber,
                        JoinDate = r.JoinDate,
                        UserName = r.UserName,
                        RegularDate = r.RegularDate,
                        MentorCode = r.MentorCode,
                        MentorName = r.MentorName,
                        HighMentorCode = r.HighMentorCode,
                        HighMentorName = r.HighMentorName,
                        ResultRound1 = r.ResultRound1,
                        ResultRound2 = r.ResultRound2,
                        ResultRound3 = r.ResultRound3,
                        ResultRound4 = r.ResultRound4,
                        TripResult5 = t.TripResult5,
                        ResultStatus = t.ResultStatus,
                        Organization1 = r.FullPathText
                    }).MergeTable().OrderBy(r => r.JoinDate, OrderByType.Desc);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            var workNumbers = listViewDto.Select(t => t.WorkNumber).ToList();
            var list = await _db.Queryable<TrialUserDetailEntity>().Where(t => workNumbers.Contains(t.WorkNumber)).ToListAsync();

            Parallel.ForEach(listViewDto, trialUser =>
            {
                var orgs = trialUser.Organization1?.Split('_');
                if (orgs != null)
                {
                    trialUser.Organization2 = orgs.Length >= 2 ? orgs[1] : string.Empty;
                    trialUser.Organization3 = orgs.Length >= 3 ? orgs[2] : string.Empty;
                    trialUser.Organization4 = orgs.Length >= 4 ? orgs[3] : string.Empty;
                    trialUser.Organization5 = orgs.Length >= 5 ? orgs[4] : string.Empty;
                    trialUser.Organization6 = orgs.Length >= 6 ? orgs[5] : string.Empty;
                    trialUser.Organization7 = orgs.Length >= 7 ? orgs[6] : string.Empty;
                    trialUser.Organization8 = orgs.Length > 7 ? orgs[7] : string.Empty;
                }

                #region 每个阶段计划制总结

                if (!trialUser.ResultRound1.IsNullOrEmpty())
                {
                    TrialUserDetailEntity firstResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound1);
                    if (firstResultRound != null)
                    {
                        trialUser.MonthSummaryResult1 = firstResultRound.MonthSummaryResult;
                    }
                }

                if (!trialUser.ResultRound2.IsNullOrEmpty())
                {
                    TrialUserDetailEntity secondResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound2);
                    if (secondResultRound != null)
                    {
                        trialUser.MonthSummaryResult2 = secondResultRound.MonthSummaryResult;
                    }
                }

                if (!trialUser.ResultRound3.IsNullOrEmpty())
                {
                    TrialUserDetailEntity thirdResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound3);
                    if (thirdResultRound != null)
                    {
                        trialUser.MonthSummaryResult3 = thirdResultRound.MonthSummaryResult;
                    }
                }

                if (!trialUser.ResultRound4.IsNullOrEmpty())
                {
                    TrialUserDetailEntity fourResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound4);
                    if (fourResultRound != null)
                    {
                        trialUser.MonthSummaryResult4 = fourResultRound.MonthSummaryResult;
                    }
                }
                #endregion
            });

            //返回结果
            return new PagedResultDto<GetSummarResultOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 导出试用期结果汇总表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportSummarResultAsync(GetSummarResultInput input)
        {
            input.PageSize = int.MaxValue;
            var data = await GetSummarResult(input);
            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/试用期结果汇总表模板.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);
            #region // 边框样式

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            #endregion

            for (int i = 0; i < data.Items.Count; i++)
            {
                List<string> columns = new List<string>();
                columns.Add(data.Items[i].Organization2);
                columns.Add(data.Items[i].Organization3);
                columns.Add(data.Items[i].Organization4);
                columns.Add(data.Items[i].Organization5);
                columns.Add(data.Items[i].Organization6);
                columns.Add(data.Items[i].Organization7);
                columns.Add(data.Items[i].Organization8);
                columns.Add(data.Items[i].WorkNumber);
                columns.Add(data.Items[i].UserName);
                columns.Add(data.Items[i].JoinDate?.ToString("yyyy-MM-dd"));
                columns.Add(data.Items[i].RegularDate?.ToString("yyyy-MM-dd"));
                columns.Add(data.Items[i].MentorCode);
                columns.Add(data.Items[i].MentorName);
                columns.Add(data.Items[i].HighMentorCode);
                columns.Add(data.Items[i].HighMentorName);
                columns.Add(data.Items[i].MonthSummaryResult1);
                columns.Add(data.Items[i].MonthSummaryResult2);
                columns.Add(data.Items[i].MonthSummaryResult3);
                columns.Add(data.Items[i].MonthSummaryResult4);
                columns.Add(data.Items[i].TripResult5);
                columns.Add(data.Items[i].ResultStatus == 1 ? "已转正" : "未转正");
                IRow row = sheet.CreateRow(i + 2);
                for (int j = 0; j < columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(columns[j]);
                    cell.CellStyle = borderStyle;
                }
            }

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        #endregion

        #region 适用期个人明细
        /// <summary>
        /// 根据工号查询员工适期情况明细表
        /// </summary>
        /// <param name="workNumber">工号</param>
        /// <returns></returns>
        public async Task<SummarResultDetail> GetSummarResultDetailAsync(string workNumber)
        {
            TrialUsers trialUser = await _db.Queryable<TrialUsers>().FirstAsync(t => t.WorkNumber == workNumber);
            SummarResultDetail summarResultDetail = new SummarResultDetail();
            UserExtension user = await _dbPlatform.Queryable<UserExtension>().FirstAsync(t => t.Sapemployeeid == workNumber);
            summarResultDetail.WorkNumber = workNumber;
            summarResultDetail.UserName = trialUser.UserName;
            summarResultDetail.Organization2 = user?.Stext_2;
            summarResultDetail.Organization3 = user?.Stext_3;
            summarResultDetail.Organization4 = user?.Stext_4;
            summarResultDetail.Organization5 = user?.Stext_5;
            summarResultDetail.Organization6 = user?.Stext_6;
            summarResultDetail.Organization7 = user?.Stext_7;
            summarResultDetail.Organization8 = user?.Stext_8;
            summarResultDetail.ExprotDate = DateTime.Now.ToString("yyyy-MM-dd");
            summarResultDetail.RoundName = EnumExtension.GetDescription(typeof(TrialUserRoundEnum), trialUser.Status ?? 0);
            summarResultDetail.JoinDate = trialUser?.JoinDate?.ToString("yyyy-MM-dd");
            summarResultDetail.RegularDate = trialUser?.RegularDate?.ToString("yyyy-MM-dd");
            summarResultDetail.MentorCode = trialUser?.MentorCode;
            summarResultDetail.MentorName = trialUser?.MentorName;
            summarResultDetail.ManageCode = trialUser?.ManageCode;
            summarResultDetail.ManageName = trialUser?.ManageName;
            summarResultDetail.HighMentorCode = trialUser?.HighMentorCode;
            summarResultDetail.HighMentorName = trialUser?.HighMentorName;

            List<TrialUserDetailEntity> list = await _db.Queryable<TrialUserDetailEntity>().Where(t => t.WorkNumber == workNumber).ToListAsync();

            #region 每个阶段计划制定与总结
            {
                if (!trialUser.DrawRound1.IsNullOrEmpty())
                {
                    TrialUserDetailEntity firstDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound1);
                    if (firstDrawRound != null)
                    {
                        summarResultDetail.MonthPlan1 = firstDrawRound.MonthPlan;
                        summarResultDetail.DrawTeacherApprove1 = firstDrawRound.MonthPlanTeacherApprove;
                        summarResultDetail.DrawLeaderApprove1 = firstDrawRound.MonthPlanLeaderApprove;
                        summarResultDetail.DrawHighTeacherApprove1 = firstDrawRound.MonthPlanHighTeacherApprove;
                    }
                }
                if (!trialUser.ResultRound1.IsNullOrEmpty())
                {
                    TrialUserDetailEntity firstResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound1);
                    if (firstResultRound != null)
                    {
                        summarResultDetail.MonthSummary1 = firstResultRound.MonthSummary;
                        summarResultDetail.ResultTeacherApprove1 = firstResultRound.MonthSummaryTeacherApprove;
                        summarResultDetail.ResultLeaderApprove1 = firstResultRound.MonthSummaryLeaderApprove;
                        summarResultDetail.ResultHighTeacherApprove1 = firstResultRound.MonthSummaryHighTeacherApprove;
                        summarResultDetail.MonthSummaryResult1 = firstResultRound.MonthSummaryResult;
                    }
                }
            }

            {
                if (!trialUser.DrawRound2.IsNullOrEmpty())
                {
                    TrialUserDetailEntity secondDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound2);
                    if (secondDrawRound != null)
                    {
                        summarResultDetail.MonthPlan2 = secondDrawRound.MonthPlan;
                        summarResultDetail.DrawTeacherApprove2 = secondDrawRound.MonthPlanTeacherApprove;
                        summarResultDetail.DrawLeaderApprove2 = secondDrawRound.MonthPlanLeaderApprove;
                        summarResultDetail.DrawHighTeacherApprove2 = secondDrawRound.MonthPlanHighTeacherApprove;
                    }
                }

                if (!trialUser.ResultRound2.IsNullOrEmpty())
                {
                    TrialUserDetailEntity secondResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound2);
                    if (secondResultRound != null)
                    {
                        summarResultDetail.MonthSummary2 = secondResultRound.MonthSummary;
                        summarResultDetail.ResultTeacherApprove2 = secondResultRound.MonthSummaryTeacherApprove;
                        summarResultDetail.ResultLeaderApprove2 = secondResultRound.MonthSummaryLeaderApprove;
                        summarResultDetail.ResultHighTeacherApprove2 = secondResultRound.MonthSummaryHighTeacherApprove;
                        summarResultDetail.MonthSummaryResult2 = secondResultRound.MonthSummaryResult;
                    }
                }
            }

            {
                if (!trialUser.DrawRound3.IsNullOrEmpty())
                {
                    TrialUserDetailEntity thirdDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound3);
                    if (thirdDrawRound != null)
                    {
                        summarResultDetail.MonthPlan3 = thirdDrawRound.MonthPlan;
                        summarResultDetail.DrawTeacherApprove3 = thirdDrawRound.MonthPlanTeacherApprove;
                        summarResultDetail.DrawLeaderApprove3 = thirdDrawRound.MonthPlanLeaderApprove;
                        summarResultDetail.DrawHighTeacherApprove3 = thirdDrawRound.MonthPlanHighTeacherApprove;
                    }
                }

                if (!trialUser.ResultRound3.IsNullOrEmpty())
                {
                    TrialUserDetailEntity thirdResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound3);
                    if (thirdResultRound != null)
                    {
                        summarResultDetail.MonthSummary3 = thirdResultRound.MonthSummary;
                        summarResultDetail.ResultTeacherApprove3 = thirdResultRound.MonthSummaryTeacherApprove;
                        summarResultDetail.ResultLeaderApprove3 = thirdResultRound.MonthSummaryLeaderApprove;
                        summarResultDetail.ResultHighTeacherApprove3 = thirdResultRound.MonthSummaryHighTeacherApprove;
                        summarResultDetail.MonthSummaryResult3 = thirdResultRound.MonthSummaryResult;
                    }
                }
            }

            {
                if (!trialUser.DrawRound4.IsNullOrEmpty())
                {
                    TrialUserDetailEntity fourDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound4);
                    if (fourDrawRound != null)
                    {
                        summarResultDetail.MonthPlan4 = fourDrawRound.MonthPlan;
                        summarResultDetail.DrawTeacherApprove4 = fourDrawRound.MonthPlanTeacherApprove;
                        summarResultDetail.DrawLeaderApprove4 = fourDrawRound.MonthPlanLeaderApprove;
                        summarResultDetail.DrawHighTeacherApprove4 = fourDrawRound.MonthPlanHighTeacherApprove;
                    }
                }

                if (!trialUser.ResultRound4.IsNullOrEmpty())
                {
                    TrialUserDetailEntity fourResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound4);
                    if (fourResultRound != null)
                    {
                        summarResultDetail.MonthSummary4 = fourResultRound.MonthSummary;
                        summarResultDetail.ResultTeacherApprove4 = fourResultRound.MonthSummaryTeacherApprove;
                        summarResultDetail.ResultLeaderApprove4 = fourResultRound.MonthSummaryLeaderApprove;
                        summarResultDetail.ResultHighTeacherApprove4 = fourResultRound.MonthSummaryHighTeacherApprove;
                        summarResultDetail.MonthSummaryResult4 = fourResultRound.MonthSummaryResult;
                    }
                }
            }

            #endregion

            TrialUserTripEntity trialUserTrip = await _db.Queryable<TrialUserTripEntity>().FirstAsync(t => t.WorkNumber == workNumber);
            if (trialUserTrip != null)
            {
                summarResultDetail.ValuesPracticedName = trialUserTrip.ValuesPracticedName;
                summarResultDetail.TripResult1 = trialUserTrip.TripResult1;
                summarResultDetail.TripResult2 = trialUserTrip.TripResult2;
                summarResultDetail.TripResult3 = trialUserTrip.TripResult3;
                summarResultDetail.TripResult4 = trialUserTrip.TripResult4;
                summarResultDetail.TripResult5 = trialUserTrip.TripResult5;
                summarResultDetail.ResultStatusName = trialUserTrip.ResultStatus == 1 ? "已转正" : "不予以转正";
            }

            return summarResultDetail;
        }


        /// <summary>
        /// 根据工号导出员工适期情况明细表
        /// </summary>
        /// <param name="workNumber">工号</param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportSummarResultDetailAsync(string workNumber)
        {
            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/试用期个人明细表模板.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);
            var detail = await GetSummarResultDetailAsync(workNumber);

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            borderStyle.Alignment = HorizontalAlignment.Center;
            borderStyle.VerticalAlignment = VerticalAlignment.Justify;
            borderStyle.WrapText = true;

            ICell cell;
            IRow row = sheet.CreateRow(4);
            for (int i = 2; i < 11; i++)
            {
                cell = row.CreateCell(i + 1);
                cell.CellStyle = borderStyle;
            }

            row.GetCell(3).SetCellValue(detail.Organization2 ?? string.Empty);
            row.GetCell(4).SetCellValue(detail.Organization3 ?? string.Empty);
            row.GetCell(5).SetCellValue(detail.Organization4 ?? string.Empty);
            row.GetCell(6).SetCellValue(detail.Organization5 ?? string.Empty);
            row.GetCell(7).SetCellValue(detail.Organization6 ?? string.Empty);
            row.GetCell(8).SetCellValue(detail.Organization7 ?? string.Empty);
            row.GetCell(9).SetCellValue(detail.Organization8 ?? string.Empty);
            row.GetCell(10).SetCellValue(detail.ExprotDate ?? string.Empty);
            row.GetCell(11).SetCellValue(detail.RoundName ?? string.Empty);

            row = sheet.CreateRow(6);
            for (int i = 2; i < 11; i++)
            {
                cell = row.CreateCell(i + 1);
                cell.CellStyle = borderStyle;
            }

            row.GetCell(3).SetCellValue(detail.WorkNumber ?? string.Empty);
            row.GetCell(4).SetCellValue(detail.UserName ?? string.Empty);
            row.GetCell(5).SetCellValue(detail.JoinDate ?? string.Empty);
            row.GetCell(6).SetCellValue(detail.RegularDate ?? string.Empty);
            row.GetCell(7).SetCellValue(detail.MentorCode ?? string.Empty);
            row.GetCell(8).SetCellValue(detail.MentorName ?? string.Empty);
            row.GetCell(9).SetCellValue($"{detail.ManageCode} {detail.ManageName}");
            row.GetCell(10).SetCellValue(detail.HighMentorCode ?? string.Empty);
            row.GetCell(11).SetCellValue(detail.HighMentorName ?? string.Empty);

            row = sheet.GetRow(7);
            cell = row.GetCell(11);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.MonthSummaryResult1);
            row = sheet.GetRow(9);
            row.GetCell(3).SetCellValue(detail.MonthPlan1);
            row.GetCell(7).SetCellValue(detail.MonthSummary1);
            SetRowHight(row);
            row = sheet.GetRow(11);
            row.GetCell(3).SetCellValue(detail.DrawTeacherApprove1);
            row.GetCell(7).SetCellValue(detail.ResultTeacherApprove1);
            SetRowHight(row);
            row = sheet.GetRow(13);
            row.GetCell(3).SetCellValue(detail.DrawLeaderApprove1);
            row.GetCell(7).SetCellValue(detail.ResultLeaderApprove1);
            SetRowHight(row);
            row = sheet.GetRow(15);
            row.GetCell(3).SetCellValue(detail.DrawHighTeacherApprove1);
            row.GetCell(7).SetCellValue(detail.ResultHighTeacherApprove1);
            SetRowHight(row);

            row = sheet.GetRow(16);
            cell = row.GetCell(11);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.MonthSummaryResult2);
            row = sheet.GetRow(18);
            row.GetCell(3).SetCellValue(detail.MonthPlan2);
            row.GetCell(7).SetCellValue(detail.MonthSummary2);
            SetRowHight(row);
            row = sheet.GetRow(20);
            row.GetCell(3).SetCellValue(detail.DrawTeacherApprove2);
            row.GetCell(7).SetCellValue(detail.ResultTeacherApprove2);
            SetRowHight(row);
            row = sheet.GetRow(22);
            row.GetCell(3).SetCellValue(detail.DrawLeaderApprove2);
            row.GetCell(7).SetCellValue(detail.ResultLeaderApprove2);
            SetRowHight(row);
            row = sheet.GetRow(24);
            row.GetCell(3).SetCellValue(detail.DrawHighTeacherApprove2);
            row.GetCell(7).SetCellValue(detail.ResultHighTeacherApprove2);
            SetRowHight(row);

            row = sheet.GetRow(25);
            cell = row.GetCell(11);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.MonthSummaryResult3);
            row = sheet.GetRow(27);
            row.GetCell(3).SetCellValue(detail.MonthPlan3);
            row.GetCell(7).SetCellValue(detail.MonthSummary3);
            SetRowHight(row);
            row = sheet.GetRow(29);
            row.GetCell(3).SetCellValue(detail.DrawTeacherApprove3);
            row.GetCell(7).SetCellValue(detail.ResultTeacherApprove3);
            SetRowHight(row);
            row = sheet.GetRow(31);
            row.GetCell(3).SetCellValue(detail.DrawLeaderApprove3);
            row.GetCell(7).SetCellValue(detail.ResultLeaderApprove3);
            SetRowHight(row);
            row = sheet.GetRow(33);
            row.GetCell(3).SetCellValue(detail.DrawHighTeacherApprove3);
            row.GetCell(7).SetCellValue(detail.ResultHighTeacherApprove3);
            SetRowHight(row);

            row = sheet.GetRow(34);
            cell = row.GetCell(11);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.MonthSummaryResult4);
            row = sheet.GetRow(36);
            row.GetCell(3).SetCellValue(detail.MonthPlan4);
            row.GetCell(7).SetCellValue(detail.MonthSummary4);
            SetRowHight(row);
            row = sheet.GetRow(38);
            row.GetCell(3).SetCellValue(detail.DrawTeacherApprove4);
            row.GetCell(7).SetCellValue(detail.ResultTeacherApprove4);
            SetRowHight(row);
            row = sheet.GetRow(40);
            row.GetCell(3).SetCellValue(detail.DrawLeaderApprove4);
            row.GetCell(7).SetCellValue(detail.ResultLeaderApprove4);
            SetRowHight(row);
            row = sheet.GetRow(42);
            row.GetCell(3).SetCellValue(detail.DrawHighTeacherApprove4);
            row.GetCell(7).SetCellValue(detail.ResultHighTeacherApprove4);
            SetRowHight(row);

            row = sheet.GetRow(45);
            cell = row.GetCell(3);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.ValuesPracticedName);

            cell = row.GetCell(4);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.TripResult1);

            cell = row.GetCell(5);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.TripResult2);

            cell = row.GetCell(6);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.TripResult3);

            cell = row.GetCell(7);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.TripResult4);

            cell = row.GetCell(8);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.ValuesPracticedName);

            cell = row.GetCell(9);
            cell.CellStyle = borderStyle;
            cell.SetCellValue(detail.ResultStatusName);

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        #endregion

        #region 适用期转正评估明细表

        /// <summary>
        /// 适用期转正评估明细查询数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<GetRegularDetailOutput>> GetRegularResultAsync(GetSummarResultInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = _db.Queryable<TrialUsers, TrialUserTripEntity>(
                    (r, t) => new JoinQueryInfos(
                        JoinType.Left, r.WorkNumber == t.WorkNumber))
                .Where((r, t) => r.TU)
                .WhereIF(!input.WorkNumber.IsNullOrEmpty(), (r, t) => r.WorkNumber.Contains(input.WorkNumber))
                .WhereIF(!input.UserName.IsNullOrEmpty(), (r, t) => r.UserName.Contains(input.UserName))
                .WhereIF(!input.Mentor.IsNullOrEmpty(), (r, t) =>
                r.MentorCode.Contains(input.Mentor) ||
                r.MentorName.Contains(input.Mentor) ||
                r.HighMentorCode.Contains(input.Mentor) ||
                r.HighMentorName.Contains(input.Mentor)
                )
                  .WhereIF(input.JoinStartDate != null, (r, t) => input.JoinStartDate <= r.JoinDate)
                  .WhereIF(input.JoinEndDate != null, (r, t) => input.JoinEndDate >= r.JoinDate)
                  .WhereIF(input.RegularStartDate != null, (r, t) => input.RegularStartDate <= r.RegularDate)
                  .WhereIF(input.RegularEndDate != null, (r, t) => input.RegularEndDate >= r.RegularDate)
                  .WhereIF(input.Round != null && input.Round != 100, (r, t) => r.Status == input.Round)
                  .WhereIF(input.Round != null && input.Round == 100, (r, t) => r.Status < 6)
                  .WhereIF(input.Department != null, (r, t) => r.FullPathText.Contains(input.Department))

                .Select((r, t) =>
                    new GetRegularDetailOutput()
                    {
                        WorkNumber = r.WorkNumber,
                        JoinDate = r.JoinDate,
                        UserName = r.UserName,
                        RegularDate = r.RegularDate,
                        MentorCode = r.MentorCode,
                        MentorName = r.MentorName,
                        HighMentorCode = r.HighMentorCode,
                        HighMentorName = r.HighMentorName,
                        Organization1 = r.FullPathText,
                        ValuesPracticedName = t.ValuesPracticedName,
                        TripResult1 = t.TripResult1,
                        TripResult2 = t.TripResult2,
                        TripResult3 = t.TripResult3,
                        TripResult4 = t.TripResult4,
                        TripResult5 = t.TripResult5,
                        ResultStatusName = t.ResultStatus == 1 ? "已转正" : "不予以转正"
                    }).MergeTable().OrderBy(r => r.JoinDate, OrderByType.Desc);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            listViewDto?.ForEach(item =>
            {
                var orgs = item.Organization1?.Split('_');
                if (orgs != null)
                {
                    item.Organization2 = orgs.Length >= 2 ? orgs[1] : string.Empty;
                    item.Organization3 = orgs.Length >= 3 ? orgs[2] : string.Empty;
                    item.Organization4 = orgs.Length >= 4 ? orgs[3] : string.Empty;
                    item.Organization5 = orgs.Length >= 5 ? orgs[4] : string.Empty;
                    item.Organization6 = orgs.Length >= 6 ? orgs[5] : string.Empty;
                    item.Organization7 = orgs.Length >= 7 ? orgs[6] : string.Empty;
                    item.Organization8 = orgs.Length > 7 ? orgs[7] : string.Empty;
                }
            });

            //返回结果
            return new PagedResultDto<GetRegularDetailOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        /// <summary>
        /// 导出适用期转正评估明细
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportRegularResultAsync(GetSummarResultInput input)
        {
            input.PageSize = int.MaxValue;
            var data = await GetRegularResultAsync(input);
            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/试用期转正评估明细表模板.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);
            #region // 边框样式

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            #endregion

            for (int i = 0; i < data.Items.Count; i++)
            {
                List<string> columns = new List<string>();
                columns.Add(data.Items[i].Organization2);
                columns.Add(data.Items[i].Organization3);
                columns.Add(data.Items[i].Organization4);
                columns.Add(data.Items[i].Organization5);
                columns.Add(data.Items[i].Organization6);
                columns.Add(data.Items[i].Organization7);
                columns.Add(data.Items[i].Organization8);
                columns.Add(data.Items[i].WorkNumber);
                columns.Add(data.Items[i].UserName);
                columns.Add(data.Items[i].JoinDate?.ToString("yyyy-MM-dd"));
                columns.Add(data.Items[i].RegularDate?.ToString("yyyy-MM-dd"));
                columns.Add(data.Items[i].MentorCode);
                columns.Add(data.Items[i].MentorName);
                columns.Add(data.Items[i].HighMentorCode);
                columns.Add(data.Items[i].HighMentorName);
                columns.Add(data.Items[i].ValuesPracticedName);
                columns.Add(data.Items[i].TripResult1);
                columns.Add(data.Items[i].TripResult2);
                columns.Add(data.Items[i].TripResult3);
                columns.Add(data.Items[i].TripResult4);
                columns.Add(data.Items[i].TripResult5);
                columns.Add(data.Items[i].ResultStatusName);
                IRow row = sheet.CreateRow(i + 2);
                for (int j = 0; j < columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(columns[j]);
                    cell.CellStyle = borderStyle;
                }
            }

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }
        #endregion

        #region 适用期月度评估明细表
        /// <summary>
        /// 查询试用期月度评估明细表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<GetTrialDetailOutput>> GetTrialDetailAsync(GetSummarResultInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = _db.Queryable<TrialUsers, TrialUserTripEntity>(
                    (r, t) => new JoinQueryInfos(
                        JoinType.Left, r.WorkNumber == t.WorkNumber))
                .Where((r, t) => r.TU)
                .WhereIF(!input.WorkNumber.IsNullOrEmpty(), (r, t) => r.WorkNumber.Contains(input.WorkNumber))
                .WhereIF(!input.UserName.IsNullOrEmpty(), (r, t) => r.UserName.Contains(input.UserName))
                .WhereIF(!input.Mentor.IsNullOrEmpty(), (r, t) =>
                r.MentorCode.Contains(input.Mentor) ||
                r.MentorName.Contains(input.Mentor) ||
                r.HighMentorCode.Contains(input.Mentor) ||
                r.HighMentorName.Contains(input.Mentor)
                )
                  .WhereIF(input.JoinStartDate != null, (r, t) => input.JoinStartDate <= r.JoinDate)
                  .WhereIF(input.JoinEndDate != null, (r, t) => input.JoinEndDate >= r.JoinDate)
                  .WhereIF(input.RegularStartDate != null, (r, t) => input.RegularStartDate <= r.RegularDate)
                  .WhereIF(input.RegularEndDate != null, (r, t) => input.RegularEndDate >= r.RegularDate)
                  .WhereIF(input.Round != null && input.Round != 100, (r, t) => r.Status == input.Round)
                  .WhereIF(input.Round != null && input.Round == 100, (r, t) => r.Status < 6)
                  .WhereIF(input.Department != null, (r, t) => r.FullPathText.Contains(input.Department))

                .Select((r, t) =>
                    new GetTrialDetailOutput()
                    {
                        WorkNumber = r.WorkNumber,
                        JoinDate = r.JoinDate,
                        UserName = r.UserName,
                        RegularDate = r.RegularDate,
                        MentorCode = r.MentorCode,
                        MentorName = r.MentorName,
                        HighMentorCode = r.HighMentorCode,
                        HighMentorName = r.HighMentorName,
                        MonthSummaryResult1 = t.MonthSummaryResult1,
                        MonthSummaryResult2 = t.MonthSummaryResult2,
                        MonthSummaryResult3 = t.MonthSummaryResult3,
                        MonthSummaryResult4 = t.MonthSummaryResult4,
                        TripResult5 = t.TripResult5,
                        ResultStatus = t.ResultStatus,
                        Organization1 = r.FullPathText,
                        DrawRound1 = r.DrawRound1,
                        DrawRound2 = r.DrawRound2,
                        DrawRound3 = r.DrawRound3,
                        DrawRound4 = r.DrawRound4,
                        ResultRound1 = r.ResultRound1,
                        ResultRound2 = r.ResultRound2,
                        ResultRound3 = r.ResultRound3,
                        ResultRound4 = r.ResultRound4
                    }).MergeTable().OrderBy(r => r.JoinDate, OrderByType.Desc);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            var workNumbers = listViewDto.Select(t => t.WorkNumber).ToList();

            var list = await _db.Queryable<TrialUserDetailEntity>().Where(t => workNumbers.Contains(t.WorkNumber)).ToListAsync();

            Parallel.ForEach(listViewDto, trialUser =>
            {
                var orgs = trialUser.Organization1?.Split('_');
                if (orgs != null)
                {
                    trialUser.Organization2 = orgs.Length >= 2 ? orgs[1] : string.Empty;
                    trialUser.Organization3 = orgs.Length >= 3 ? orgs[2] : string.Empty;
                    trialUser.Organization4 = orgs.Length >= 4 ? orgs[3] : string.Empty;
                    trialUser.Organization5 = orgs.Length >= 5 ? orgs[4] : string.Empty;
                    trialUser.Organization6 = orgs.Length >= 6 ? orgs[5] : string.Empty;
                    trialUser.Organization7 = orgs.Length >= 7 ? orgs[6] : string.Empty;
                    trialUser.Organization8 = orgs.Length > 7 ? orgs[7] : string.Empty;
                }

                #region 每个阶段计划制定与总结
                {
                    if (!trialUser.DrawRound1.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity firstDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound1);
                        if (firstDrawRound != null)
                        {
                            trialUser.MonthPlan1 = firstDrawRound.MonthPlan;
                            trialUser.DrawTeacherApprove1 = firstDrawRound.MonthPlanTeacherApprove;
                            trialUser.DrawLeaderApprove1 = firstDrawRound.MonthPlanLeaderApprove;
                            trialUser.DrawHighTeacherApprove1 = firstDrawRound.MonthPlanHighTeacherApprove;
                        }
                    }
                    if (!trialUser.ResultRound1.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity firstResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound1);
                        if (firstResultRound != null)
                        {
                            trialUser.MonthSummary1 = firstResultRound.MonthSummary;
                            trialUser.ResultTeacherApprove1 = firstResultRound.MonthSummaryTeacherApprove;
                            trialUser.ResultLeaderApprove1 = firstResultRound.MonthSummaryLeaderApprove;
                            trialUser.ResultHighTeacherApprove1 = firstResultRound.MonthSummaryHighTeacherApprove;
                            trialUser.MonthSummaryResult1 = firstResultRound.MonthSummaryResult;
                        }
                    }
                }

                {
                    if (!trialUser.DrawRound2.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity secondDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound2);
                        if (secondDrawRound != null)
                        {
                            trialUser.MonthPlan2 = secondDrawRound.MonthPlan;
                            trialUser.DrawTeacherApprove2 = secondDrawRound.MonthPlanTeacherApprove;
                            trialUser.DrawLeaderApprove2 = secondDrawRound.MonthPlanLeaderApprove;
                            trialUser.DrawHighTeacherApprove2 = secondDrawRound.MonthPlanHighTeacherApprove;
                        }
                    }

                    if (!trialUser.ResultRound2.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity secondResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound2);
                        if (secondResultRound != null)
                        {
                            trialUser.MonthSummary2 = secondResultRound.MonthSummary;
                            trialUser.ResultTeacherApprove2 = secondResultRound.MonthSummaryTeacherApprove;
                            trialUser.ResultLeaderApprove2 = secondResultRound.MonthSummaryLeaderApprove;
                            trialUser.ResultHighTeacherApprove2 = secondResultRound.MonthSummaryHighTeacherApprove;
                            trialUser.MonthSummaryResult2 = secondResultRound.MonthSummaryResult;
                        }
                    }
                }

                {
                    if (!trialUser.DrawRound3.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity thirdDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound3);
                        if (thirdDrawRound != null)
                        {
                            trialUser.MonthPlan3 = thirdDrawRound.MonthPlan;
                            trialUser.DrawTeacherApprove3 = thirdDrawRound.MonthPlanTeacherApprove;
                            trialUser.DrawLeaderApprove3 = thirdDrawRound.MonthPlanLeaderApprove;
                            trialUser.DrawHighTeacherApprove3 = thirdDrawRound.MonthPlanHighTeacherApprove;
                        }
                    }

                    if (!trialUser.ResultRound3.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity thirdResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound3);
                        if (thirdResultRound != null)
                        {
                            trialUser.MonthSummary3 = thirdResultRound.MonthSummary;
                            trialUser.ResultTeacherApprove3 = thirdResultRound.MonthSummaryTeacherApprove;
                            trialUser.ResultLeaderApprove3 = thirdResultRound.MonthSummaryLeaderApprove;
                            trialUser.ResultHighTeacherApprove3 = thirdResultRound.MonthSummaryHighTeacherApprove;
                            trialUser.MonthSummaryResult3 = thirdResultRound.MonthSummaryResult;
                        }
                    }
                }

                {
                    if (!trialUser.DrawRound4.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity fourDrawRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.DrawRound4);
                        if (fourDrawRound != null)
                        {
                            trialUser.MonthPlan4 = fourDrawRound.MonthPlan;
                            trialUser.DrawTeacherApprove4 = fourDrawRound.MonthPlanTeacherApprove;
                            trialUser.DrawLeaderApprove4 = fourDrawRound.MonthPlanLeaderApprove;
                            trialUser.DrawHighTeacherApprove4 = fourDrawRound.MonthPlanHighTeacherApprove;
                        }
                    }

                    if (!trialUser.ResultRound4.IsNullOrEmpty())
                    {
                        TrialUserDetailEntity fourResultRound = list.FirstOrDefault(t => t.InstanceNumber == trialUser.ResultRound4);
                        if (fourResultRound != null)
                        {
                            trialUser.MonthSummary4 = fourResultRound.MonthSummary;
                            trialUser.ResultTeacherApprove4 = fourResultRound.MonthSummaryTeacherApprove;
                            trialUser.ResultLeaderApprove4 = fourResultRound.MonthSummaryLeaderApprove;
                            trialUser.ResultHighTeacherApprove4 = fourResultRound.MonthSummaryHighTeacherApprove;
                            trialUser.MonthSummaryResult4 = fourResultRound.MonthSummaryResult;
                        }
                    }
                }

                #endregion
            });

            return new PagedResultDto<GetTrialDetailOutput>()
            {
                Items = listViewDto,
                TotalCount = totalCount
            };
        }

        /// <summary>
        /// 导出试用期月度评估明细表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportTrialDetailAsync(GetSummarResultInput input)
        {
            input.PageSize = int.MaxValue;
            var data = await GetTrialDetailAsync(input);
            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/试用期月度评估明细表模板.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);
            #region // 边框样式

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            #endregion

            for (int i = 0; i < data.Items.Count; i++)
            {
                List<string> columns = new List<string>();
                columns.Add(data.Items[i].Organization2);
                columns.Add(data.Items[i].Organization3);
                columns.Add(data.Items[i].Organization4);
                columns.Add(data.Items[i].Organization5);
                columns.Add(data.Items[i].Organization6);
                columns.Add(data.Items[i].Organization7);
                columns.Add(data.Items[i].Organization8);
                columns.Add(data.Items[i].WorkNumber);
                columns.Add(data.Items[i].UserName);
                columns.Add(data.Items[i].JoinDate?.ToString("yyyy-MM-dd"));
                columns.Add(data.Items[i].RegularDate?.ToString("yyyy-MM-dd"));
                columns.Add(data.Items[i].MentorCode);
                columns.Add(data.Items[i].MentorName);
                columns.Add(data.Items[i].HighMentorCode);
                columns.Add(data.Items[i].HighMentorName);
                columns.Add(data.Items[i].MonthPlan1);
                columns.Add(data.Items[i].DrawTeacherApprove1);
                columns.Add(data.Items[i].DrawLeaderApprove1);
                columns.Add(data.Items[i].DrawHighTeacherApprove1);
                columns.Add(data.Items[i].MonthPlan1.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].DrawTeacherApprove1.IsNullOrEmpty() ? string.Empty : "已完成");
                columns.Add(data.Items[i].MonthSummary1);
                columns.Add(data.Items[i].ResultTeacherApprove1);
                columns.Add(data.Items[i].ResultLeaderApprove1);
                columns.Add(data.Items[i].ResultHighTeacherApprove1);
                columns.Add(data.Items[i].MonthSummary1.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].ResultTeacherApprove1.IsNullOrEmpty() ? string.Empty : "已完成");
                columns.Add(data.Items[i].MonthPlan2);
                columns.Add(data.Items[i].DrawTeacherApprove2);
                columns.Add(data.Items[i].DrawLeaderApprove2);
                columns.Add(data.Items[i].DrawHighTeacherApprove2);
                columns.Add(data.Items[i].MonthPlan2.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].DrawTeacherApprove2.IsNullOrEmpty() ? string.Empty : "已完成");
                columns.Add(data.Items[i].MonthSummary2);
                columns.Add(data.Items[i].ResultTeacherApprove2);
                columns.Add(data.Items[i].ResultLeaderApprove2);
                columns.Add(data.Items[i].ResultHighTeacherApprove2);
                columns.Add(data.Items[i].MonthSummary2.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].ResultTeacherApprove2.IsNullOrEmpty() ? string.Empty : "已完成");
                columns.Add(data.Items[i].MonthPlan3);
                columns.Add(data.Items[i].DrawTeacherApprove3);
                columns.Add(data.Items[i].DrawLeaderApprove3);
                columns.Add(data.Items[i].DrawHighTeacherApprove3);
                columns.Add(data.Items[i].MonthPlan3.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].DrawTeacherApprove3.IsNullOrEmpty() ? string.Empty : "已完成");
                columns.Add(data.Items[i].MonthSummary3);
                columns.Add(data.Items[i].ResultTeacherApprove3);
                columns.Add(data.Items[i].ResultLeaderApprove3);
                columns.Add(data.Items[i].ResultHighTeacherApprove3);
                columns.Add(data.Items[i].MonthSummary3.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].ResultTeacherApprove3.IsNullOrEmpty() ? string.Empty : "已完成");
                columns.Add(data.Items[i].MonthPlan4);
                columns.Add(data.Items[i].DrawTeacherApprove4);
                columns.Add(data.Items[i].DrawLeaderApprove4);
                columns.Add(data.Items[i].DrawHighTeacherApprove4);
                columns.Add(data.Items[i].MonthPlan4.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].DrawTeacherApprove4.IsNullOrEmpty() ? string.Empty : "已完成");
                columns.Add(data.Items[i].MonthSummary4);
                columns.Add(data.Items[i].ResultTeacherApprove4);
                columns.Add(data.Items[i].ResultLeaderApprove4);
                columns.Add(data.Items[i].ResultHighTeacherApprove4);
                columns.Add(data.Items[i].MonthSummary4.IsNullOrEmpty() ? "未完成" : "已完成");
                columns.Add(data.Items[i].ResultTeacherApprove4.IsNullOrEmpty() ? string.Empty : "已完成");
                IRow row = sheet.CreateRow(i + 2);
                for (int j = 0; j < columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(columns[j]);
                    cell.CellStyle = borderStyle;
                }
            }

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        #endregion

        #region 总部报表
        /// <summary>
        /// 总部报表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<GetGeneralResultOutput>> GetGeneralResultAsync(GetSummarResultInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = _db.Queryable<TrialUsers, TrialUserTripEntity>(
                    (r, t) => new JoinQueryInfos(
                        JoinType.Inner, r.WorkNumber == t.WorkNumber))
                .Where((r, t) => r.TU == false)
                .WhereIF(!input.WorkNumber.IsNullOrEmpty(), (r, t) => r.WorkNumber.Contains(input.WorkNumber))
                .WhereIF(!input.UserName.IsNullOrEmpty(), (r, t) => r.UserName.Contains(input.UserName))
                .WhereIF(!input.Mentor.IsNullOrEmpty(), (r, t) =>
                r.MentorCode.Contains(input.Mentor) ||
                r.MentorName.Contains(input.Mentor) ||
                r.HighMentorCode.Contains(input.Mentor) ||
                r.HighMentorName.Contains(input.Mentor)
                )
                  .WhereIF(input.JoinStartDate != null, (r, t) => input.JoinStartDate <= r.JoinDate)
                  .WhereIF(input.JoinEndDate != null, (r, t) => input.JoinEndDate >= r.JoinDate)
                  .WhereIF(input.RegularStartDate != null, (r, t) => input.RegularStartDate <= r.RegularDate)
                  .WhereIF(input.RegularEndDate != null, (r, t) => input.RegularEndDate >= r.RegularDate)
                  .WhereIF(input.Round != null && input.Round != 100, (r, t) => r.Status == input.Round)
                  .WhereIF(input.Round != null && input.Round == 100, (r, t) => r.Status < 6)
                  .WhereIF(input.Department != null, (r, t) => r.FullPathText.Contains(input.Department))

                .Select((r, t) =>
                    new GetGeneralResultOutput()
                    {
                        WorkNumber = r.WorkNumber,
                        UserName = r.UserName,
                        ApproveStatusName = r.Status == 6 ? "审批通过" : "审批拒绝",
                        RegularDate = r.RegularDate,
                        InstanceNumber = t.InstanceNumber,
                        ResultStatus = t.ResultStatus,
                        Organization1 = r.FullPathText
                    }).MergeTable().OrderBy(r => r.RegularDate, OrderByType.Desc);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            listViewDto?.ForEach(item =>
            {
                var orgs = item.Organization1?.Split('_');
                if (orgs != null)
                {
                    item.Organization2 = orgs.Length >= 2 ? orgs[1] : string.Empty;
                    item.Organization3 = orgs.Length >= 3 ? orgs[2] : string.Empty;
                    item.Organization4 = orgs.Length >= 4 ? orgs[3] : string.Empty;
                    item.Organization5 = orgs.Length >= 5 ? orgs[4] : string.Empty;
                    item.Organization6 = orgs.Length >= 6 ? orgs[5] : string.Empty;
                    item.Organization7 = orgs.Length >= 7 ? orgs[6] : string.Empty;
                    item.Organization8 = orgs.Length > 7 ? orgs[7] : string.Empty;
                }

                // item.ApproveStatusName = item.ResultStatus == 1 || item.ResultStatus == 2 ? "审批通过" : "审批拒绝";
                if (item.ResultStatus == 1)
                {
                    item.ResultStatusName = "合格";
                }
                else if (item.ResultStatus == 2)
                {
                    item.ResultStatusName = "优秀";
                }
                else if (item.ResultStatus == 0)
                {
                    item.ResultStatusName = "不合格";
                }
            });

            //返回结果
            return new PagedResultDto<GetGeneralResultOutput>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        public async Task<List<GetGeneralResultOutput>> ExportGeneralResultAsync(GetSummarResultInput input)
        {
            var listQueryable = _db.Queryable<TrialUsers, TrialUserTripEntity>(
                               (r, t) => new JoinQueryInfos(
                                   JoinType.Left, r.WorkNumber == t.WorkNumber))
                           .Where((r, t) => r.TU == false)
                           .WhereIF(!input.WorkNumber.IsNullOrEmpty(), (r, t) => r.WorkNumber.Contains(input.WorkNumber))
                           .WhereIF(!input.UserName.IsNullOrEmpty(), (r, t) => r.UserName.Contains(input.UserName))
                           .WhereIF(!input.Mentor.IsNullOrEmpty(), (r, t) =>
                           r.MentorCode.Contains(input.Mentor) ||
                           r.MentorName.Contains(input.Mentor) ||
                           r.HighMentorCode.Contains(input.Mentor) ||
                           r.HighMentorName.Contains(input.Mentor)
                           )
                             .WhereIF(input.JoinStartDate != null, (r, t) => input.JoinStartDate <= r.JoinDate)
                             .WhereIF(input.JoinEndDate != null, (r, t) => input.JoinEndDate >= r.JoinDate)
                             .WhereIF(input.RegularStartDate != null, (r, t) => input.RegularStartDate <= r.RegularDate)
                             .WhereIF(input.RegularEndDate != null, (r, t) => input.RegularEndDate >= r.RegularDate)
                             .WhereIF(input.Round != null && input.Round != 100, (r, t) => r.Status == input.Round)
                             .WhereIF(input.Round != null && input.Round == 100, (r, t) => r.Status < 6)
                             .WhereIF(input.Department != null, (r, t) => r.FullPathText.Contains(input.Department))

                           .Select((r, t) =>
                               new GetGeneralResultOutput()
                               {
                                   WorkNumber = r.WorkNumber,
                                   UserName = r.UserName,
                                   RegularDate = r.RegularDate,
                                   InstanceNumber = t.InstanceNumber,
                                   ResultStatus = t.ResultStatus
                               }).MergeTable().OrderBy(r => r.RegularDate, OrderByType.Desc);
            var listViewDto = await listQueryable.ToListAsync();

            listViewDto?.ForEach(item =>
            {
                item.ApproveStatusName = item.ResultStatus == 1 || item.ResultStatus == 2 ? "审批通过" : "审批拒绝";
                if (item.ResultStatus == 1)
                {
                    item.ResultStatusName = "合格";
                }
                else if (item.ResultStatus == 2)
                {
                    item.ResultStatusName = "优秀";
                }
                else if (item.ResultStatus == 0)
                {
                    item.ResultStatusName = "不合格";
                }
            });
            return listViewDto;
        }

        #endregion

        #region 私有方法
        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="row">行信息</param>
        /// <returns></returns>
        private void SetRowHight(IRow row)
        {
            double hight = 0;
            int baseCount = 1;
            for (int i = row.Cells.Count() - 1; i >= 0; i--)
            {
                ICell currentCell = row.Cells[i];
                if (currentCell.CellType == CellType.Blank)
                {
                    baseCount++;
                    continue;
                }

                int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
                int count = (int)(length / (20 * baseCount)) + 1;
                double temphight = 14.25 * count;
                baseCount = 1;
                if (temphight > hight)
                {
                    hight = temphight;
                }
            }

            row.HeightInPoints = (float)hight;
        }

        #endregion
    }
}